package org.tysf.gt.utils;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import org.tysf.gt.pojo.Flat;
import org.tysf.gt.pojo.StudentMsg;
import org.tysf.gt.service.IFlatService;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

@Component
public class ExcelReaderUtil {
    private static Logger logger = Logger.getLogger(ExcelReaderUtil.class.getName());
    //兼容老版本的excel文件格式
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    @Resource
    private IFlatService flatService;

    /**
     * 根据文件后缀名解析相应的excel文件
     *
     * @param inputStream 文件输入流
     * @param fileType    excel文件后缀类型
     * @return 包含文件数据的工作簿对象
     */
    public Workbook getWorkBook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        //兼容老版本的excel
        if (fileType.equalsIgnoreCase("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    public List<StudentMsg> readExcel(MultipartFile file) throws IOException {
        Workbook workbook = null;
        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null || originalFilename.isEmpty() || originalFilename.lastIndexOf(".") < 0) {
            logger.warning("excel文件非法");
            return null;
        }
        String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
        workbook = getWorkBook(file.getInputStream(), fileType);
        List<StudentMsg> studentMsgs = parseExcel(workbook);
        return studentMsgs;
    }

    public List<StudentMsg> parseExcel(Workbook workbook) {
        List<StudentMsg> resultDataList = new ArrayList<>();
        //为了方便只解析第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            return null;
        }
        //获取第一行数据所在的行号,第一行为标题
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (firstRow == null) {
            logger.warning("excel文件格式错误！");
        }
        //解析数据区域
        int rowStart = firstRowNum + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }

            StudentMsg studentMsg = convertRowToStuMsg(row);
            resultDataList.add(studentMsg);
        }
        return resultDataList;
    }

    public StudentMsg convertRowToStuMsg(Row row) {
        StudentMsg studentMsg = new StudentMsg();
        Cell cell = null;
        int cellNum = 0;
        //获取学号
        cell = row.getCell(cellNum++);
        String stuNo = convertCellValueToString(cell);
        studentMsg.setStuid(stuNo);
        //获取名字
        cell = row.getCell(cellNum++);
        String name = convertCellValueToString(cell);
        studentMsg.setSname(name);
        //获取公寓
        cell = row.getCell(cellNum++);
        Flat flat = convertCellToFlat(cell);
        studentMsg.setFlat(flat);
        //获取宿舍
        cell = row.getCell(cellNum++);
        String dormitory = convertCellValueToString(cell);
        studentMsg.setDormitory(dormitory);
        //获取电话号码
        cell = row.getCell(cellNum);
        String Tel = convertCellValueToString(cell);
        studentMsg.setTel(Tel);
        //设置初始密码值
        String password=HanyuPinyinHelper.getFirstLettersUp(name+stuNo);
        password=MD5Utils.md5Password(password);
        studentMsg.setPassword(password);
        return studentMsg;
    }

    /**
     * 转换cell对象为String类型
     *
     * @param cell 需要转换的cell对象
     * @return cell对象相应的字符串
     */
    public String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING: //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN: //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK: // 空值
                break;
            case FORMULA: // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR: // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    public Flat convertCellToFlat(Cell cell) {
        if (cell == null) {
            return null;
        }
        Flat flat = null;
        String stringCellValue = cell.getStringCellValue();
        List<Flat> flats = flatService.queryFlatAll();
        for (Flat flat1 : flats
        ) {
            if (flat1.getFname().contentEquals(stringCellValue)) {
                flat = flat1;
                break;
            }
        }
        return flat;
    }

}
